package com.netbao.ins.vomapper;

import com.netbao.ins.mapper.InsuranceMapper;
import com.netbao.ins.model.Insurance;
import com.netbao.ins.model.InsuranceVo;
import com.netbao.sys.model.Dict;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @create 2019-11-04 16:12
 * @author Duanps
 */
public interface InsuranceVoMapper extends InsuranceMapper {
    @Select("SELECT\n" +
            "  ins_insurance.id,\n" +
            "  ins_insurance.idcard,\n" +
            "  ins_insurance.cno,\n" +
            "  ins_insurance.cname,\n" +
            "  ins_insurance.stime,\n" +
            "  ins_insurance.etime,\n" +
            "  dic1.cname ctypeName,\n" +
            "  dic2.cname companyName,\n" +
            "  dic3.cname ccycleName,\n" +
            "  veh_captain.cname captainName,\n" +
            "  veh_captain.phone captainPhone,\n" +
            "  veh_driver.cname driverName,\n" +
            "  veh_driver.phone driverPhone\n" +
            "FROM\n" +
            "  ins_insurance,\n" +
            "  sys_dict dic1,\n" +
            "  sys_dict dic2,\n" +
            "  sys_dict dic3,\n" +
            "  veh_captain ,\n" +
            "  veh_driver\n" +
            "WHERE\n" +
            "      ins_insurance.ctype = dic1.id\n" +
            "      AND ins_insurance.company = dic2.id\n" +
            "      AND ins_insurance.ccycle = dic3.id\n" +
            "      AND ins_insurance.idcard = veh_driver.idcard\n" +
            "      AND veh_captain.phone = ins_insurance.captian_phone")
    List<InsuranceVo> findAll(@Param("cno") String cno, @Param("idCard") String idCard);
    @Select("SELECT id,idcard FROM veh_vehicle WHERE veh_vehicle.idcard  NOT IN (SELECT idcard FROM ins_insurance);")
    List<Insurance> getInsuList();

    @Select("SELECT vd.cname driverName,vd.phone driverPhone,vc.cname captainName,vc.phone captainPhone FROM veh_captain vc,veh_driver vd,veh_vehicle vv WHERE vv.phone = vc.phone AND vd.idcard = vv.idcard AND vv.id = #{id}")
    InsuranceVo getInsuListData(Integer id);

    /**
     * 查询保险周期
     * @param
     * @return
     */
    @Select("SELECT * FROM sys_dict WHERE pid='100'")
    List<Dict> getInsuranceYear();

    @Select("SELECT * FROM sys_dict WHERE pid='200'")
    List<Dict> getInsuranceType();

    @Select("SELECT * FROM sys_dict WHERE pid='300'")
    List<Dict> getInsuranceCompany();
    @Select("SELECT\n" +
            "             ins_insurance.id,\n" +
            "             ins_insurance.idcard,\n" +
            "              ins_insurance.cno,\n" +
            "              ins_insurance.cname,\n" +
            "              ins_insurance.stime,\n" +
            "              ins_insurance.etime,\n" +
            "              dic1.cname ctypeName,\n" +
            "              dic2.cname companyName,\n" +
            "              dic3.cname ccycleName,\n" +
            "              veh_captain.cname captainName,\n" +
            "              veh_captain.phone captainPhone,\n" +
            "              veh_driver.cname driverName,\n" +
            "              veh_driver.phone driverPhone\n" +
            "            FROM\n" +
            "              ins_insurance,\n" +
            "              sys_dict dic1,\n" +
            "              sys_dict dic2,\n" +
            "              sys_dict dic3,\n" +
            "              veh_captain ,\n" +
            "              veh_driver\n" +
            "            WHERE\n" +
            "                  ins_insurance.ctype = dic1.id\n" +
            "                 AND ins_insurance.company = dic2.id\n" +
            "                  AND ins_insurance.ccycle = dic3.id\n" +
            "                  AND ins_insurance.idcard = veh_driver.idcard\n" +
            "                AND veh_captain.phone = ins_insurance.captian_phone AND ins_insurance.`idcard` LIKE \"%${idcard}%\"\n" +
            "\n")
    List<InsuranceVo> mo(String idcard);

}
